package dao;



import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: cheng
 * @date: 2023/7/5 14:46
 * @description: file_meta表封装操作
 */
public class FileDao {
    //1.初始化数据库
    public void initDB(){
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            String[] sqls = getInitSql();
            for (String sql: sqls) {
                System.out.println("[initDB] sql "+ sql);
                statement.executeUpdate(sql);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,null);
        }
    }

    //1.1从db.sql读取文件内容(注意要动态获取)
    private String[] getInitSql(){
        StringBuilder sb = new StringBuilder();
        try (InputStream inputStream = FileDao.class.getClassLoader().getResourceAsStream("db.sql")){
            try (InputStreamReader inputStreamReader = new InputStreamReader(inputStream,"utf8")){
                while (true){
                    int ch = inputStreamReader.read();//read()返回值是int 但它实际是char
                    if(ch == -1){
                        break; //文件读取完
                    }
                    sb.append((char) ch);
                }
            }
        }catch (IOException e){
            e.printStackTrace();
        }
        return sb.toString().split(";");
    }

    //2.插入文件(一次插入多条)
    public void add(List<FileMeta> fileMetas){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            //关闭自动提交
            connection.setAutoCommit(false);

            String sql = "insert into file_meta values(null,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            for (FileMeta fileMeta : fileMetas){
                preparedStatement.setString(1,fileMeta.getName());
                preparedStatement.setString(2,fileMeta.getPath());
                preparedStatement.setBoolean(3,fileMeta.isDirectory());
                preparedStatement.setString(4,fileMeta.getPinyin());
                preparedStatement.setString(5,fileMeta.getPinyinFirst());
                preparedStatement.setLong(6,fileMeta.getSize());
                preparedStatement.setTimestamp(7,new Timestamp(fileMeta.getLastModified()));
                //构造好的sql累计起来
                preparedStatement.addBatch();

                System.out.println("[insert] 插入文件 " + fileMeta.getPath() + File.separator + fileMeta.getName());
            }

            preparedStatement.executeBatch();
            //告知数据库添加完毕
            connection.commit();
        }catch (SQLException e){
            //异常,进行回滚
            try {
                //假设连接就出现异常,那么就不必进行回滚
                if(connection != null){
                    connection.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DBUtil.close(connection,preparedStatement,null);
        }
    }

    //3.查询(根据关键词进行查询)
    public List<FileMeta> searchByPattern(String pattern){
        List<FileMeta> fileMetas = new ArrayList<>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select name, path, is_directory, size, last_modified from file_meta"
                       + " where name like ? or pinyin like ? or pinyin_first like ? "
                       + "order by path, name";
            statement = connection.prepareStatement(sql);
            statement.setString(1,"%" + pattern + "%");
            statement.setString(2,"%" + pattern + "%");
            statement.setString(3,"%" + pattern + "%");
            //单个sql
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                String path = resultSet.getString("path");
                boolean isDirectory = resultSet.getBoolean("is_directory");
                long size = resultSet.getLong("size");
                Timestamp lastModified = resultSet.getTimestamp("last_modified");
                FileMeta fileMeta = new FileMeta(name,path,isDirectory,size,lastModified.getTime());
                fileMetas.add(fileMeta);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }

        return fileMetas;
    }

    //4.指定路径查询
    public List<FileMeta> searchByPath(String targetPath){
        List<FileMeta> fileMetas = new ArrayList<>();

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select name, path, is_directory, size, last_modified from file_meta"
                       + " where path = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,targetPath);
            resultSet = statement.executeQuery();
            while(resultSet.next()){
                String name = resultSet.getString("name");
                String path = resultSet.getString("path");
                boolean isDirectory = resultSet.getBoolean("is_directory");
                long size = resultSet.getLong("size");
                Timestamp lastModified = resultSet.getTimestamp("last_modified");
                FileMeta fileMeta = new FileMeta(name,path,isDirectory,size,lastModified.getTime());
                fileMetas.add(fileMeta);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }

        return fileMetas;
    }

    //删除(扫描文件时,发现文件已经没了,要把表里数据更新)
    //有可能是一个文件(此时直接删除表),若是目录,则要将目录内的所有文件都删除
    public void delete(List<FileMeta> fileMetas){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            //关闭自动提交
            connection.setAutoCommit(false);

            for (FileMeta fileMeta : fileMetas){
                String sql = null;
                if(!fileMeta.isDirectory()){
                    //删除文件
                    sql = "delete from file_meta where name=? and path=?";
                }else {
                    //删除目录
                    sql = "delete from file_meta where (name=? and path=?) or (path like ?)";
                }

                statement = connection.prepareStatement(sql);

                if (!fileMeta.isDirectory()){
                    //文件替换 ?
                     statement.setString(1,fileMeta.getName());
                     statement.setString(2,fileMeta.getPath());
                }else {
                    //目录
                     statement.setString(1,fileMeta.getName());
                     statement.setString(2,fileMeta.getPath());
                     //File.separator 类似于 d://test 里的 /
                     statement.setString(3,fileMeta.getPath() + File.separator + fileMeta.getName() + File.separator + "%");
                }
                //执行
                statement.executeUpdate();
                System.out.println("[delete] " + fileMeta.getPath() + fileMeta.getName());
                //有多个statement
                statement.close();
            }
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            DBUtil.close(connection,null,null);
        }
    }
}
